TD 2 : SQL

SQL Interrogation de données

WarningAvec solutions

L3 MIASHS

Université Paris Cité

Année 2025

Course Homepage

Moodle

Objectifs

• requêtes avec jointures internes,
• requêtes avec WITH (CTEs).

Prérequis

• schéma world,

• cours Algèbre relationnelle,

• cours Requêtes simples,

• cours Requêtes imbriquées, CTEs.

Questions

1. Quels sont les noms des capitales Sud-Américaines ? (14 lignes)

TipSolution

SELECT name
FROM world.city JOIN world.country ON capital=id
WHERE continent = 'South America';

2. Quels sont les noms des pays où le français est langue officielle ? (18 lignes)

TipSolution

SELECT name_country
FROM world.country JOIN world.countrylanguage USING (countrycode)
WHERE isofficial AND language='French';

3. Quels sont les pays dont le gouvernement est monarchique (constitutionnelle ou pas) ? (43 lignes)

TipSolution

SELECT name_country 
FROM world.country 
WHERE governmentform LIKE '%Monarchy%';

4. Quelles sont les monarchies d’Europe dont la capitale compte moins d’un million d’habitants ? (8 lignes)

TipSolution

WITH T1 AS (
  SELECT *
  FROM world.country
  WHERE governmentform LIKE '%Monarchy%' AND continent='Europe' 
)
SELECT name_country
FROM T1 JOIN world.city ON capital=id
WHERE population < 1000000;

5. Quelles sont les régions où on ne trouve pas de monarchie ? (9 lignes)

TipSolution

WITH monarchy AS 
(
  SELECT region
  FROM world.country
  WHERE governmentform LIKE '%Monarchy%'
)
(
  SELECT region 
  FROM world.country
) 
EXCEPT 
(
  SELECT * 
  FROM monarchy
);

6. Quelles sont les langues qui ne sont langues officielles dans aucun pays ? (355 lignes)

TipSolution

WITH R AS (
  SELECT language 
  FROM world.countrylanguage 
  WHERE isofficial
)
(
  SELECT DISTINCT language 
  FROM world.countrylanguage
) 
EXCEPT 
(
  SELECT * 
  FROM R
);

7. Quels sont les pays qui n’ont pas de langue officielle ? (49 lignes)

TipSolution

WITH R AS (
  SELECT countrycode 
  FROM world.countrylanguage
  WHERE isofficial
),
S AS (
  SELECT countrycode 
  FROM world.country
  EXCEPT 
  SELECT * FROM R
)
SELECT name_country 
FROM world.country JOIN S USING (countrycode);

8. Quels sont les pays dont la seule langue officielle est le français ? (9 lignes)

TipSolution

WITH R AS(
  SELECT DISTINCT T1.countrycode 
  FROM world.countrylanguage as T1 JOIN
      world.countrylanguage as T2 ON 
      T1.countrycode = T2.countrycode AND T1.language <> T2.language 
  WHERE T1.isofficial AND T2.isofficial
),
S AS (
  SELECT countrycode 
    FROM world.countrylanguage
    WHERE language='French' AND isofficial
  EXCEPT 
  SELECT * FROM R
)
SELECT name_country 
FROM world.country JOIN S USING(countrycode);

Ou bien :

WITH OnlyFrench AS (
  (SELECT countrycode
  FROM countrylanguage
  WHERE isofficial AND language = 'French')
  EXCEPT
  (SELECT countrycode
  FROM countrylanguage
  WHERE isofficial AND language <> 'French')
)
SELECT name_country 
FROM world.country JOIN OnlyFrench USING(countrycode);

9. Les noms des pays où le français n’est pas la seule langue officielle. (9 lignes)

TipSolution

WITH R AS (
  SELECT DISTINCT T1.countrycode 
  FROM world.countrylanguage as T1
  JOIN world.countrylanguage as T2
      ON T1.countrycode = T2.countrycode
  WHERE T1.language <> T2.language AND
      T1.language = 'French' AND
      T1.isofficial AND 
      T2.isofficial
)
SELECT name_country 
FROM R JOIN world.country USING(countrycode);

En utilisant INTERSECT :

WITH AlsoFrench AS (
  (SELECT countrycode
  FROM world.countrylanguage
  WHERE isofficial AND language = 'French')
  INTERSECT
  (SELECT countrycode
  FROM world.countrylanguage
  WHERE isofficial AND language <> 'French')
)
SELECT name_country 
FROM world.country JOIN AlsoFrench USING(countrycode);

10. Quelles sont les régions qui ne comportent qu’une seule forme de gouvernement ? (3 lignes)

TipSolution

WITH R AS (
  SELECT T1.region 
  FROM world.country AS T1 JOIN 
       world.country AS T2 ON T1.region = T2.region
  WHERE T1.governmentform <> T2.governmentform
)
SELECT region 
FROM world.country 
EXCEPT 
SELECT * 
FROM R;

11. Quelles sont les langues officielles des pays dont la capitale compte plus de 5 000 000 d’habitants ? (12 lignes)

TipSolution

SELECT DISTINCT language
FROM world.country co JOIN world.city ci ON co.capital=ci.id
     JOIN world.countrylanguage cl ON co.countrycode=cl.countrycode
WHERE population > 5000000 and isofficial;

12. Quels sont les pays où au moins trois langues sont parlées chacune par strictement plus de \(10\%\) de la population ? (35 lignes)

TipSolution

WITH R AS (
  SELECT T1.countrycode 
  FROM  world.countrylanguage AS T1 
        JOIN world.countrylanguage AS T2 ON T1.countrycode = T2.countrycode 
        JOIN world.countrylanguage AS T3 ON T1.countrycode = T3.countrycode
  WHERE T1.language <> T2.language AND
        T2.language <> T3.language AND
        T1.language <> T3.language AND
        T1.percentage > 10 AND
        T2.percentage > 10 AND
        T3.percentage > 10
)
SELECT DISTINCT name_country 
FROM world.country JOIN R USING(countrycode);

ou

WITH tpc AS (
  SELECT *
  FROM world.countrylanguage
  WHERE percentage > 10), 
three AS (
  SELECT DISTINCT t1.countrycode
  FROM tpc AS t1  
   JOIN tpc AS t2 ON (t1.countrycode=t2.countrycode AND t1.language<t2.language) 
   JOIN tpc AS t3 ON (t1.countrycode=t3.countrycode AND t3.language>t2.language)
)
SELECT co.name_country
FROM world.country co JOIN three USING(countrycode);

13. Quelles sont les régions où il existe deux pays dont les espérances de vie diffèrent par au moins \(10\) ans ? (16 lignes)

TipSolution

SELECT DISTINCT T1.region
FROM world.country AS T1 JOIN world.country AS T2 
     ON T1.region = T2.region AND T1.countrycode <> T2.countrycode
WHERE ABS(T1.lifeexpectancy - T2.lifeexpectancy) >= 10;

14. Quels sont les pays où l’anglais et le français sont des langues officielles ? (3 lignes)

TipSolution

WITH R AS (
  SELECT T1.countrycode
  FROM world.countrylanguage AS T1 JOIN 
       world.countrylanguage AS T2 USING (countrycode)
  WHERE T1.language='French' AND 
        T2.language='English' AND 
        T1.isofficial AND 
        T2.isofficial
)
SELECT name_country 
FROM R JOIN world.country USING (countrycode);

15. Montrer comment calculer l’intersection de deux tables avec une jointure (sous certaines conditions).

TipSolution

Si R et S sont deux tables de même schéma, on pourrait croire qu’une jointure naturelle des deux tables permet de calculer leur intersection.

Pourtant, du fait de la logique trivalente, si certaines colonnes contiennent des valeurs nulles

(SELECT * FROM R)
INTERSECT
(SELECT * FROM S)   ;

ne donne pas le même résultat que

SELECT *
FROM    R NATURAL JOIN S ;

Dans la deuxième requêtes, tous les tuples possédant au moins un attribut NULL sont rejetés car la condition de jointure est alors UNKNOWN.

Il faut limiter la jointure aux attributs qui identifient chaque ligne d’une table, ce qu’on appelle la clé primaire, pour lesquels la définition de la table interdit la valeur NULL.

Par exemple pour l’intersection de la table country avec elle-même :

(SELECT * FROM country) 
INTERSECT 
(SELECT * FROM country);

retourne la table country, soit 239 lignes.

La requête

SELECT * 
FROM country AS c1 JOIN 
     country AS c2
     ON c1.countrycode = c2.countrycode;

retourne égalemement les 239 lignes de la table country.

Mais la jointure naturelle

SELECT * 
FROM country AS c1 NATURAL JOIN 
     country AS c2;

ne donne que 167 lignes, les tuples qui n’ont aucun attribut NULL.